package com.bbs2.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import com.bbs2.entity.Board;
import com.bbs2.entity.Post;
import com.bbs2.entity.User;
import com.bbs2.util.DBHelp;
import com.bbs2.util.Page;
import com.bbs2.util.RowMapper;

public class PostDao {
	private DBHelp<Post> db = new DBHelp<Post>();
	public int getBoardSize(String boardId){
		String sql = "SELECT COUNT(*) FROM t_post WHERE t_board_id = ?";
		return db.queryForInt(sql, boardId);
	}
	public List<Post> findByBoardId(String id,Page page){
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT p.id,title,createTime,content,p.flag,clickNum,t_board_id,t_user_id,u.id AS uid,u.name,u.password,u.email,u.flag AS uflag,u.pic,b.id AS bid,b.name AS bname,b.flag AS bflag  ");
		sql.append("FROM t_post AS p ");
		sql.append("LEFT JOIN t_user AS u ON p.t_user_id =u.id ");
		sql.append("LEFT JOIN t_board AS b ON p.t_board_id = b.id ");
		sql.append("WHERE p.t_board_id=? ");
		sql.append("LIMIT ?,? ");
		return db.queryForList(sql.toString(), new PostMap(), id,page.getStartNum(),page.getPageSize());
	}
	public List<Post> findByBoardId(String id){
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT p.id,title,createTime,content,p.flag,clickNum,t_board_id,t_user_id,u.id AS uid,u.name,u.password,u.email,u.flag AS uflag,u.pic,b.id AS bid,b.name AS bname,b.flag AS bflag ");
		sql.append("FROM t_post AS p ");
		sql.append("LEFT JOIN t_user AS u ON p.t_user_id =u.id ");
		sql.append("LEFT JOIN t_board AS b ON p.t_board_id = b.id ");
		sql.append("WHERE p.t_board_id=? ");
		return db.queryForList(sql.toString(), new PostMap(), id);
	}
	public int save(Post p){
		String sql = "INSERT INTO t_post(title,createTime,content,t_board_id,t_user_id) VALUES(?,?,?,?,?)";
		return  db.getLastInsertId(sql, p.getTitle(),p.getCreateTime(),p.getContent(),p.getBoardId(),p.getUserId());
	}
	public Post findById(String id){
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT p.id,title,createTime,content,p.flag,clickNum,t_board_id,t_user_id,u.id AS uid,u.name,u.password,u.email,u.flag AS uflag,u.pic,b.id AS bid,b.name AS bname,b.flag AS bflag ");
		sql.append("FROM t_post AS p ");
		sql.append("LEFT JOIN t_user AS u ON p.t_user_id =u.id ");
		sql.append("LEFT JOIN t_board AS b ON p.t_board_id = b.id ");
		sql.append("WHERE p.id=?");
		return db.queryForObject(sql.toString(), new PostMap(), id);
	}
	public int update(Post p){
		String sql = "UPDATE t_post SET title=?,content=?,flag=?,clickNum=?,t_board_id=? WHERE id=?";
		return db.executeUpdate(sql, p.getTitle(),p.getContent(),p.getFlag(),p.getClickNum(),p.getBoardId(),p.getId());
	}
	public int del(String id){
		String sql = "DELETE FROM t_post WHERE id=?";
		return db.executeUpdate(sql, id);
	}
}
class PostMap implements RowMapper<Post>{

	public Post mapRow(ResultSet rs) throws SQLException {
		Post p = new Post();
		p.setId(rs.getInt("id"));
		p.setTitle(rs.getString("title"));
		p.setCreateTime(rs.getString("createTime"));
		p.setContent(rs.getString("content"));
		p.setFlag(rs.getInt("flag"));
		p.setClickNum(rs.getInt("clickNum"));
		p.setBoardId(rs.getInt("t_board_id"));
		p.setUserId(rs.getInt("t_user_id"));
		
		User user = new User();
		user.setId(rs.getInt("uid"));
		user.setName(rs.getString("name"));
		user.setPassword(rs.getString("password"));
		user.setEmail(rs.getString("email"));
		user.setFlag(rs.getInt("uflag"));
		p.setUser(user);
		
		Board b = new Board();
		b.setId(rs.getInt("bid"));
		b.setName(rs.getString("bname"));
		b.setFlag(rs.getInt("bflag"));
		p.setBoard(b);
		return p;
	}
	
}